"""
成绩分析API - 基于真实数据的成绩分析接口
"""
from flask import request, jsonify
from flask_jwt_extended import jwt_required, get_jwt_identity
from app.blueprints.api import api_bp
from app.models.user import User
from app.models.course import Course
from app.models.student_course import StudentCourse
from app.extensions import db
from sqlalchemy import func, desc


@api_bp.route('/grades/student/<int:student_id>', methods=['GET'])
@jwt_required()
def get_student_grades(student_id):
    """获取学生的所有成绩"""
    try:
        # 验证权限
        current_user_id = get_jwt_identity()
        current_user = User.query.get(current_user_id)
        
        # 只允许学生本人、教师或管理员查看
        if current_user.role == 'student' and current_user.id != student_id:
            return jsonify({
                'success': False,
                'message': '无权查看其他学生的成绩'
            }), 403
        
        # 获取筛选参数
        academic_year = request.args.get('academic_year')
        semester_term = request.args.get('semester_term')
        
        # 构建查询
        query = StudentCourse.query.filter_by(student_id=student_id)
        
        if academic_year:
            query = query.filter_by(academic_year=academic_year)
        if semester_term:
            query = query.filter_by(semester_term=semester_term)
        
        grades = query.order_by(
            desc(StudentCourse.academic_year),
            desc(StudentCourse.semester_term)
        ).all()
        
        # 组织返回数据
        result = []
        for grade in grades:
            course = Course.query.get(grade.course_id)
            result.append({
                'id': grade.id,
                'course_code': course.code,
                'course_name': course.name,
                'credits': float(course.credits),
                'academic_year': grade.academic_year,
                'semester_term': grade.semester_term,
                'exam_type': grade.exam_type,
                'credit_type': grade.credit_type,
                'score': float(grade.score) if grade.score else None,
                'gpa': float(grade.gpa) if grade.gpa else None,
                'letter_grade': grade.letter_grade,
                'status': grade.status,
                'course_category': course.course_category,
                'department': course.department
            })
        
        return jsonify({
            'success': True,
            'data': result,
            'total': len(result)
        }), 200
        
    except Exception as e:
        return jsonify({
            'success': False,
            'message': str(e)
        }), 500


@api_bp.route('/grades/statistics/<int:student_id>', methods=['GET'])
@jwt_required()
def get_student_statistics(student_id):
    """获取学生成绩统计信息"""
    try:
        # 验证权限
        current_user_id = get_jwt_identity()
        current_user = User.query.get(current_user_id)
        
        if current_user.role == 'student' and current_user.id != student_id:
            return jsonify({
                'success': False,
                'message': '无权查看其他学生的成绩'
            }), 403
        
        # 按学年学期统计
        semester_stats = db.session.query(
            StudentCourse.academic_year,
            StudentCourse.semester_term,
            func.count(StudentCourse.id).label('course_count'),
            func.avg(StudentCourse.score).label('avg_score'),
            func.avg(StudentCourse.gpa).label('avg_gpa'),
            func.sum(Course.credits).label('total_credits')
        ).join(
            Course, StudentCourse.course_id == Course.id
        ).filter(
            StudentCourse.student_id == student_id,
            StudentCourse.status == 'completed'
        ).group_by(
            StudentCourse.academic_year,
            StudentCourse.semester_term
        ).order_by(
            desc(StudentCourse.academic_year),
            desc(StudentCourse.semester_term)
        ).all()
        
        semester_data = []
        for stat in semester_stats:
            semester_data.append({
                'academic_year': stat.academic_year,
                'semester_term': stat.semester_term,
                'course_count': stat.course_count,
                'avg_score': round(float(stat.avg_score), 2) if stat.avg_score else None,
                'avg_gpa': round(float(stat.avg_gpa), 2) if stat.avg_gpa else None,
                'total_credits': float(stat.total_credits) if stat.total_credits else 0
            })
        
        # 按课程类别统计
        category_stats = db.session.query(
            Course.course_category,
            func.count(StudentCourse.id).label('course_count'),
            func.avg(StudentCourse.score).label('avg_score'),
            func.sum(Course.credits).label('total_credits')
        ).join(
            Course, StudentCourse.course_id == Course.id
        ).filter(
            StudentCourse.student_id == student_id,
            StudentCourse.status == 'completed'
        ).group_by(
            Course.course_category
        ).all()
        
        category_data = []
        for stat in category_stats:
            category_data.append({
                'category': stat.course_category,
                'course_count': stat.course_count,
                'avg_score': round(float(stat.avg_score), 2) if stat.avg_score else None,
                'total_credits': float(stat.total_credits) if stat.total_credits else 0
            })
        
        # 总体统计
        overall = db.session.query(
            func.count(StudentCourse.id).label('total_courses'),
            func.avg(StudentCourse.score).label('overall_avg_score'),
            func.avg(StudentCourse.gpa).label('overall_avg_gpa'),
            func.sum(Course.credits).label('total_credits')
        ).join(
            Course, StudentCourse.course_id == Course.id
        ).filter(
            StudentCourse.student_id == student_id,
            StudentCourse.status == 'completed'
        ).first()
        
        return jsonify({
            'success': True,
            'data': {
                'overall': {
                    'total_courses': overall.total_courses,
                    'overall_avg_score': round(float(overall.overall_avg_score), 2) if overall.overall_avg_score else None,
                    'overall_avg_gpa': round(float(overall.overall_avg_gpa), 2) if overall.overall_avg_gpa else None,
                    'total_credits': float(overall.total_credits) if overall.total_credits else 0
                },
                'by_semester': semester_data,
                'by_category': category_data
            }
        }), 200
        
    except Exception as e:
        return jsonify({
            'success': False,
            'message': str(e)
        }), 500


@api_bp.route('/grades/courses/<int:course_id>/statistics', methods=['GET'])
@jwt_required()
def get_course_grade_statistics(course_id):
    """获取课程成绩统计"""
    try:
        course = Course.query.get_or_404(course_id)
        
        # 获取该课程的所有成绩
        grades = StudentCourse.query.filter_by(
            course_id=course_id,
            status='completed'
        ).all()
        
        if not grades:
            return jsonify({
                'success': True,
                'data': {
                    'course': course.to_dict(),
                    'statistics': None
                }
            }), 200
        
        scores = [float(g.score) for g in grades if g.score]
        
        # 统计各分数段人数
        score_distribution = {
            '优秀(90-100)': len([s for s in scores if s >= 90]),
            '良好(80-89)': len([s for s in scores if 80 <= s < 90]),
            '中等(70-79)': len([s for s in scores if 70 <= s < 80]),
            '及格(60-69)': len([s for s in scores if 60 <= s < 70]),
            '不及格(<60)': len([s for s in scores if s < 60])
        }
        
        # 计算统计指标
        avg_score = sum(scores) / len(scores) if scores else 0
        max_score = max(scores) if scores else 0
        min_score = min(scores) if scores else 0
        pass_rate = (len([s for s in scores if s >= 60]) / len(scores) * 100) if scores else 0
        
        return jsonify({
            'success': True,
            'data': {
                'course': course.to_dict(),
                'statistics': {
                    'total_students': len(grades),
                    'avg_score': round(avg_score, 2),
                    'max_score': max_score,
                    'min_score': min_score,
                    'pass_rate': round(pass_rate, 2),
                    'score_distribution': score_distribution
                }
            }
        }), 200
        
    except Exception as e:
        return jsonify({
            'success': False,
            'message': str(e)
        }), 500


@api_bp.route('/grades/academic-years', methods=['GET'])
@jwt_required()
def get_academic_years():
    """获取所有学年列表"""
    try:
        years = db.session.query(
            StudentCourse.academic_year
        ).distinct().order_by(
            desc(StudentCourse.academic_year)
        ).all()
        
        year_list = [y[0] for y in years if y[0]]
        
        return jsonify({
            'success': True,
            'data': year_list
        }), 200
        
    except Exception as e:
        return jsonify({
            'success': False,
            'message': str(e)
        }), 500

